import akshare as ak
from foftact.etf.ETF_PE_MODEL import ETF_PE_MODEL
from foftact.db.dbutil import pooled_db

from functools import lru_cache

import pandas as pd
import requests

import datetime

def collect_pe(mcode:str="000858.SZ"):
    '''
    ETF 实时行情 列表
    :return:
    '''
    funds_pe_data = fund_etf_pe_em(mcode)
    return funds_pe_data

def filter_quarter_last(funds_pe_data:pd.DataFrame):
    # 排序（确保日期是升序）
    funds_pe_data = funds_pe_data.sort_values(by='tdate')
    # 获取每个季度的最后一笔数据（不包括当前季度）
    # 1. 添加季度列
    funds_pe_data['quarter'] = funds_pe_data['date'].dt.to_period('Q')
    # 2. 找到每个季度的最后一笔数据
    quarterly_last = funds_pe_data.groupby('quarter').last().reset_index()
    # 3. 去掉当前季度的数据
    current_quarter = pd.Timestamp.today().to_period('Q')
    filtered_df = quarterly_last[quarterly_last['quarter'] < current_quarter]
    return filtered_df

    # 删除季度列
    filtered_df = filtered_df.drop(columns=['quarter'])

    # 2. 找到每个季度的最后一笔数据
    quarterly_last = funds_pe_data.groupby('quarter').last().reset_index()

def transform(fund):
    etf_pe = ETF_PE_MODEL()
    etf_pe.mcode = fund['mcode']
    etf_pe.code = fund['code']
    etf_pe.trade_date = fund['tdate']
    etf_pe.pe = fund['pe']
    etf_pe.update_time = datetime.datetime.now()

    return etf_pe

# save to db
def batch_save(etf_list, batch_size = 10, is_print=False):
    # 从连接池获取连接
    conn = pooled_db.connection()
    # 获取游标
    cursor = conn.cursor()
    # etf_list 拆分为10个一组
    for i in range(0, len(etf_list), batch_size):
        batch_etf_list = etf_list[i:i+batch_size]
        # print(f'batch_etf_list: {batch_etf_list}')
        sql_list = list()
        for row in range(0, len(batch_etf_list), 1):
            etf = batch_etf_list[row]
            sql = f'INSERT INTO etf_pe (mcode, code, update_time, trade_date, pe) VALUES \n' \
                    f' (\'{etf.mcode}\', \'{etf.code}\', \'{etf.update_time}\', \'{etf.trade_date}\', {etf.pe}) as etf_row_{row} \n' \
                    f' ON DUPLICATE KEY UPDATE name = etf_row_{row}.name, update_time = etf_row_{row}.update_time,  \n ' \
                    f' trade_date = etf_row_{row}.trade_date,  pe = etf_row_{row}.pe;'
            sql_list.append(sql)
            if is_print:
                print(f'sql: {sql}')

        if len(sql_list) > 0:
            for sql in sql_list:
                result = cursor.execute(sql)
                if is_print:
                    print(f'result: {result}')

            # 执行sql # 提交事务
            conn.commit()
            # 打印影响的行数
            if is_print:
                print(f"{cursor.rowcount} rows were inserted.")

    # 关闭游标和连接
    cursor.close()

def fund_etf_pe_em(mcode:str) -> pd.DataFrame:
    """
    东方财富-ETF 实时行情
    https://datacenter.eastmoney.com/securities/api/data/v1/get
    :return: ETF 实时行情
    :rtype: pandas.DataFrame
    """
    url = "https://datacenter.eastmoney.com/securities/api/data/v1/get"
    params = {
        'reportName': 'RPT_CUSTOM_DMSK_TREND',
        'columns': 'ALL',
        'quoteColumns' :'',
        # 'filter': f'(SECUCODE%3D"{mcode}")(INDICATORTYPE%3D1)(DATETYPE%3D4)',
        'filter': f'(SECUCODE="{mcode}")(INDICATORTYPE=1)(DATETYPE=4)',
        # 'filter': f'(SECUCODE="{mcode}")(INDICATORTYPE%3D1)(DATETYPE%3D4)',
        'pageNumber': '1',
        'pageSize':'',
        'sortTypes': '1',
        'sortColumns': 'TRADE_DATE',
        'source': 'HSF10',
        'client': 'PC',
        'v': '06855742841864056'
    }
    r = requests.get(url, timeout=15, params=params)
    data_json = r.json()
    temp_df = pd.DataFrame(data_json["result"]["data"])
    temp_df.rename(
        columns={
            "SECUCODE": "mcode",
            "TRADE_DATE": "tdate",
            "SECURITY_CODE": "code",
            "INDICATORTYPE": "type",
            "INDICATOR_VALUE": "pe"
        },
        inplace=True,
    )
    # temp_df["mcode"] = pd.to_numeric(temp_df["mcode"], errors="coerce")
    # temp_df["tdate"] = pd.to_datetime(
    #     temp_df["tdate"], format="%Y%m%d", errors="coerce"
    # )
    # temp_df["code"] = pd.to_numeric(temp_df["code"], errors="coerce")
    temp_df["type"] = pd.to_numeric(temp_df["type"], errors="coerce")
    temp_df["pe"] = pd.to_numeric(temp_df["pe"], errors="coerce")

    return temp_df


